using System;
using System.Collections.Generic;
using System.Text;
using Aspose.Excel;
using AII.Schema;
using AII.ChartReports.Grids;
using System.Data;
using System.IO;

namespace AII.ChartReports
{
	public class ExportExcelAction : AsyncAction
	{
		public string FileName;
		public List<ChartReport> ChartReports = null;
		public bool IsBreak = false;
		public bool IsManagerThenChartTypeSortMode;

		private static License excelLicence;

		static ExportExcelAction()
		{
			Stream stream = typeof(ExportPDFAction).Assembly.GetManifestResourceStream("AII.ChartReports.Aspose.Excel.lic");
			excelLicence = new License();
			excelLicence.SetLicense(stream);
		}

		public override void Execute()
		{

			Excel excel = new Excel();


			int start = 2;
			object oldid = null;
			object oldgroupid = null;
			int currentWorksheet = -1;

			foreach (GRIDRelative_Perf_Analysis cr in ChartReports)
			{

				if (IsManagerThenChartTypeSortMode)
				{

					if (!cr.ManagerID.Equals(oldid))
					{
						currentWorksheet++;
						excel.Worksheets[currentWorksheet].Name = cr.ManagerName;
						excel.Worksheets.Add();
						start = 2;
					}
					oldid = cr.ManagerID;
				}
				else
				{

					if ((!cr.ChartType.Equals(oldid)) || (!cr.GroupId.Equals(oldgroupid)))
					{
						currentWorksheet++;
						excel.Worksheets[currentWorksheet].Name = cr.ToString();
						excel.Worksheets.Add();
						start = 2;
					}
					oldid = cr.ChartType;
					oldgroupid = cr.GroupId;
				}

				Fill(excel.Worksheets[currentWorksheet], ref start, cr);
				start = start + 2;
			}

			excel.Save(FileName, FileFormatType.Default);
		}


		protected void Fill(Worksheet ws, ref int start, GRIDRelative_Perf_Analysis report)
		{
			dtoGridReport gridreport = report.reportdata;

			Cells cells = ws.Cells;
			cells.SetColumnWidth(0, 40);
			//fill header
			cells[start, 0].PutValue(report.GetChartTitle());
			cells[start, 0].Style.Font.IsBold = true;
			cells[start, 0].Style.HorizontalAlignment = TextAlignmentType.Center;
			cells.Merge(start, 0, 1, 1 + gridreport.Descriptor.Rows.Count);
			for (int i = 0; i < 1 + gridreport.Descriptor.Rows.Count; i++)
			{
				cells[start, i].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
			}
			start++;

			byte column = 1;
			byte maxcolumn = gridreport.GetMaxCount();


			string oldperiod = string.Empty;

			//set width
			//for (byte i = 1; i < gridreport.Descriptor.Rows.Count + 1; i++)
			//{
			//    cells.SetColumnWidth(i, 10);
			//}

			setrow(cells, start, gridreport.Descriptor.Rows.Count + 1, CellBorderType.Thin);

			foreach (dtoGridReport.DescriptorRow dr in gridreport.Descriptor)
			{
				if (oldperiod != dr.PeriodId)
				{
					cells.Merge(start, column, 1, (byte)maxcolumn);
					cells[start, column].PutValue(dr.PeriodTitle);
					cells[start, column].Style.HorizontalAlignment = TextAlignmentType.Center;
					bordercell(cells[start, column]);
					cells[start, column].Style.Font.IsBold = true;
					column += maxcolumn;

					cells[start, column].Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
					oldperiod = dr.PeriodId;
				}
			}

			cells[start, gridreport.Descriptor.Rows.Count].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
			cells[start, 0].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
			start++;
			setrow(cells, start, gridreport.Descriptor.Rows.Count + 1, CellBorderType.Thin);
			cells[start, 0].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
			column = 1;
			foreach (dtoGridReport.DescriptorRow dr in gridreport.Descriptor)
			{
				cells[start, column].PutValue(dr.Title);
				cells[start, column].Style.HorizontalAlignment = TextAlignmentType.Center;
				bordercell(cells[start, column]);

				if (oldperiod != dr.PeriodId)
				{
					for (int j = -2; j < 12; j++)
					{
						cells[start + j, column].Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
					}
				}

				column++;
				oldperiod = dr.PeriodId;
			}
			cells[start, gridreport.Descriptor.Rows.Count].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;

			//fill cell
			start++;
			foreach (dtoGridReport.GridReportRow gr in gridreport.GridReport)
			{
				if (gr.Title.Equals("Header"))
					continue;
				cells[start, 0].PutValue(gr.Title);
				cells[start, 0].Style.Font.IsBold = gr.IsBold;

				bordercell(cells[start, 0]);
				setrow(cells, start, gridreport.Descriptor.Rows.Count + 1, CellBorderType.Thin);
				cells[start, gridreport.Descriptor.Rows.Count].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;



				foreach (DataRowView drv in gridreport.Descriptor.DefaultView)
				{
					dtoGridReport.DescriptorRow dr = (dtoGridReport.DescriptorRow)drv.Row;


					foreach (dtoGridReport.DataRow datar in gridreport.Data.Rows)
					{
						if (datar.RowId.Equals(gr.Id) && (datar.DescriptorId.Equals(dr.Id)))
						{
							Cell cell = cells[start, (byte)dr.Order];

							if (datar.Value.IndexOf('%') > 0)
							{
								cell.PutValue(double.Parse(datar.Value.Replace('%', ' ')) / 100);
								cell.Style.Custom = "0.0%";
							}
							else
							{
								if (datar.Value.Equals("NA"))
								{
									cell.PutValue(datar.Value);
									cell.Style.Font.IsBold = true;
									cell.Style.HorizontalAlignment = TextAlignmentType.Center;
								}
								else
								{
									cell.PutValue(double.Parse(datar.Value));
								}
							}

							bordercell(cells[start, (byte)dr.Order]);

							if (oldperiod != dr.PeriodId)
							{
								cell.Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
							}
						}
					}
					oldperiod = dr.PeriodId;
				}
				cells[start, gridreport.Descriptor.Rows.Count].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
				start++;
			}

			start--;
			for (int i = 0; i < 1 + gridreport.Descriptor.Rows.Count; i++)
			{
				cells[start, i].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;

				if (i == gridreport.Descriptor.Rows.Count)
				{
					cells[start, i].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium;
				}
			}

			//set autofit width

			for (byte i = 1; i < gridreport.Descriptor.Rows.Count + 1; i++)
			{
				ws.AutoFitColumn(i);
			}

			column = 1;
			foreach (dtoGridReport.DescriptorRow dr in gridreport.Descriptor)
			{

				if (oldperiod != dr.PeriodId)
				{
					for (int j = 1; j < 29; j++)
					{
						cells[start - j, column].Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium;
					}
					oldperiod = dr.PeriodId;
				}
				column++;

			}
		}

		protected void setrow(Cells cells, int start, int count, CellBorderType celltype)
		{
			for (byte i = 0; i < count; i++)
			{
				bordercell(cells[start, i], celltype);
			}
		}

		protected void bordercell(Cell cell)
		{
			bordercell(cell, CellBorderType.Thin);
		}

		protected void bordercell(Cell cell, CellBorderType celltype)
		{
			cell.Style.Borders[BorderType.BottomBorder].LineStyle = celltype;
			cell.Style.Borders[BorderType.TopBorder].LineStyle = celltype;
			cell.Style.Borders[BorderType.LeftBorder].LineStyle = celltype;
			cell.Style.Borders[BorderType.RightBorder].LineStyle = celltype;
		}
	}
}
